package com.webmall.service;

import com.webmall.dao.BaseDao;
import com.webmall.entity.CateEntity;
import com.webmall.entity.ProdEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 专门用来处理产品的dao
 */
public class webmall_ProdDao {
    /**
     * 1.查询所有列表
     */
    public static ArrayList<ProdEntity> selectAll(){
        //声明列表及结果集
        ArrayList<ProdEntity> list= new ArrayList<ProdEntity>();
        ResultSet rs = null;//返回的结果集
        //获取连接对象
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;

        try {
            String sql= "SELECT * FROM table_product ORDER BY PRODUCT_ID";
                ps = conn.prepareStatement(sql);
                rs=ps.executeQuery();

            while (rs.next()){

                //getString:获取当前列的数据
                ProdEntity prod = new ProdEntity(
                        rs.getInt("PRODUCT_ID"),
                        rs.getString("PRODUCT_NAME"),
                        rs.getString("PRODUCT_INFO"),
                        rs.getInt("PRODUCT_PRICE"),
                        rs.getInt("PRODUCT_STOCK"),
                        rs.getInt("PRODUCT_FID"),
                        rs.getInt("PRODUCT_CID"),
                        rs.getString("PRODUCT_FILENAME")

                );
                list.add(prod);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeSql(rs,ps,conn);
        }
        return list;
    }

    /**
     * 2.添加一条prod
     * @param prod
     * @return
     */
    public static int insertProd(ProdEntity prod){

        //1。声明sql语句
        //into 表名称，后面是格式化date
        String sql = "INSERT INTO table_product VALUES(null,?,?,?,?,?,?,?) ";

        //2。调动DAO-第二个参数为object数数组
        Object[] params = {

                prod.getPRODUCT_NAME(),
                prod.getPRODUCT_INFO(),
                prod.getPRODUCT_PRICE(),
                prod.getPRODUCT_STOCK(),
                prod.getPRODUCT_FID(),
                prod.getPRODUCT_CID(),
                prod.getPRODUCT_FILENAME()
        };

        return BaseDao.sqlOperat(sql,params);
    }

    /**
     * 3.根据传入的进行搜索返回显示信息以供首页显示
     * @param cateFlag 类别标志位 0表示ID为父级 1表示ID为子级 2 表示自身id
     * @return
     */
    public static ArrayList<ProdEntity> selectByCATEId(int cateFlag,String cateid){

        ResultSet rs = null;
        CateEntity cate = null;
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ArrayList<ProdEntity> list= new ArrayList<ProdEntity>();


        try {
            String sql ="";
            if (cateFlag == 0){
                sql = "SELECT * FROM table_product WHERE PRODUCT_FID like ?";
            }else if (cateFlag == 1){
                sql = "SELECT * FROM table_product WHERE PRODUCT_CID like ?";
            }else if (cateFlag == 2){
                sql = "SELECT * FROM table_product WHERE PRODUCT_ID like ?";
            }

            ps=conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(cateid));
            rs = ps.executeQuery();

            while (rs.next()){
                ProdEntity prod = new ProdEntity(
                        rs.getInt("PRODUCT_ID"),
                        rs.getString("PRODUCT_NAME"),
                        rs.getString("PRODUCT_INFO"),
                        rs.getInt("PRODUCT_PRICE"),
                        rs.getInt("PRODUCT_STOCK"),
                        rs.getInt("PRODUCT_FID"),
                        rs.getInt("PRODUCT_CID"),
                        rs.getString("PRODUCT_FILENAME")

                );
                list.add(prod);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeSql(rs,ps,conn);
        }

        return list;

    }

    /**
     * 4.传入cate 进行信息更新
     * @param cate
     * @return
     */
    public static int updateCate(CateEntity cate){
        //1。声明sql语句
        //into 表名称，后面是格式化date
        String sql = "update table_cate set CATE_NAME=?,CATE_PARENT_ID=? where CATE_ID=?";

        //2。调动DAO-第二个参数为object数数组
        Object[] params = {
                cate.getCATE_NAME(),
                cate.getCATE_PARENT_ID(),
                cate.getCATE_ID()
        };


        //    return BaseDao.sqlOperat(sql,null);
        return BaseDao.sqlOperat(sql,params);
    }

    /**
     * 5 删除
     * @param id
     * @return
     */
    public static int deleteByID(String id){
        String sql = "delete from table_cate where CATE_ID=?";

        Object[] params = {id};

        return BaseDao.sqlOperat(sql,params);
    }

    /**
     * 6 获得所有的分类信息 进行首页展示
     * flag为father 或者child
     * @return
     */

    public static ArrayList<CateEntity> showCate(String flag){
        //声明列表及结果集
        ArrayList<CateEntity> list= new ArrayList<CateEntity>();
        ResultSet rs = null;//返回的结果集
        //获取连接对象
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;

        try {

            String sql= null;
            if (flag != null && flag.equals("father")){
                sql="SELECT * FROM table_cate WHERE CATE_PARENT_ID=0";
            }else {
                sql="SELECT * FROM table_cate WHERE CATE_PARENT_ID!=0";
            }

            ps = conn.prepareStatement(sql);
            rs=ps.executeQuery();

            while (rs.next()){

                //getString:获取当前列的数据
                CateEntity cate = new CateEntity(
                        rs.getInt("CATE_ID"),
                        rs.getString("CATE_NAME"),
                        rs.getInt("CATE_PARENT_ID")

                );
                list.add(cate);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeSql(rs,ps,conn);
        }
        return list;
    }
}

